﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class InitGridviewDA : IInitGridviewDA
    {
        private string COLUMN_INDEX = "@COLUMN_INDEX";
        private string CREATED_BY = "@CREATED_BY";
        private string CSS_CLASS = "@CSS_CLASS";
        private string DATA_FIELD = "@DATA_FIELD";
        private string FOOTER_TEXT = "@FOOTER_TEXT";
        private string FUNCTION_ID = "@FUNCTION_ID";
        private string GRIDVIEW_ID = "@GRIDVIEW_ID";
        private string HEADER_TEXT = "@HEADER_TEXT";
        private string PAGE_SIZE = "@PAGE_SIZE";
        private string SQL_SELECT_ALL_INIT_GRIDVIEW = " SELECT  IG.ROWID, IG.FUNCTION_ID, FL.MENU_STRING, IG.GRIDVIEW_ID, IG.COLUMN_INDEX, IG.HEADER_TEXT, IG.FOOTER_TEXT, IG.DATA_FIELD, IG.CSS_CLASS, IG.WIDTH, IG.PAGE_SIZE, IG.UDF1, IG.UDF2, IG.UDF3, IG.UDF4, IG.UDF5, IG.CREATED_BY, IG.CREATED_DATE, IG.UPDATED_BY, IG.UPDATED_DATE   FROM  SYSADM.INIT_GRIDVIEW IG LEFT JOIN SYSADM.FUNCTION_LIST FL ON IG.FUNCTION_ID = FL.FUNCTION_ID ";
        private string SQL_SELECT_INIT_GRIDVIEW = "SELECT DISTINCT IG.FUNCTION_ID AS FUNCTION_ID, FL.MENU_STRING AS FUNCTION_DESCR,T.[TEXT] AS FUNCTION_NAME, FL.GROUP_ID AS FUNCTION_GROUP_ID,T2.[TEXT] AS FUNCTION_GROUP_NAME, IG.GRIDVIEW_ID AS GRIDVIEW_ID FROM SYSADM.INIT_GRIDVIEW IG LEFT JOIN SYSADM.FUNCTION_LIST FL ON IG.FUNCTION_ID = FL.FUNCTION_ID LEFT JOIN SYSADM.TRANSLATION T ON 'FUNCTION_LIST.'+FL.FUNCTION_ID = T.TRANSLATION_ID AND T.LANG = 'ZH' AND T.COUNTRY='CN' LEFT JOIN SYSADM.TRANSLATION T2 ON 'FUNCTION_GROUP.'+FL.GROUP_ID = T2.TRANSLATION_ID AND T2.LANG = 'ZH' AND T2.COUNTRY='CN' ";
        private string UDF1 = "@UDF1";
        private string UDF2 = "@UDF2";
        private string UDF3 = "@UDF3";
        private string UDF4 = "@UDF4";
        private string UDF5 = "@UDF5";
        private string UPDATED_BY = "@UPDATED_BY";
        private string WIDTH = "@WIDTH";

        public List<InitGridviewInfo> GetInitGridviewByID(string functionID, string gridviewID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_INIT_GRIDVIEW + " WHERE IG.FUNCTION_ID = @FUNCTION_ID AND IG.GRIDVIEW_ID = @GRIDVIEW_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@FUNCTION_ID", functionID), new SqlParameter("@GRIDVIEW_ID", gridviewID) };
            List<InitGridviewInfo> initGridviewInfos = new List<InitGridviewInfo>();
            InitGridviewInfo initGridviewInfo = null;
            IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras);
            while (reader.Read())
            {
                DateTime CS00002;
                initGridviewInfo = new InitGridviewInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["FUNCTION_ID"].ToString(), reader["MENU_STRING"].ToString(), reader["GRIDVIEW_ID"].ToString(), (reader["COLUMN_INDEX"].ToString() != "") ? int.Parse(reader["COLUMN_INDEX"].ToString()) : 0, reader["HEADER_TEXT"].ToString(), reader["FOOTER_TEXT"].ToString(), reader["DATA_FIELD"].ToString(), reader["CSS_CLASS"].ToString(), (reader["WIDTH"].ToString() != "") ? int.Parse(reader["WIDTH"].ToString()) : 0, (reader["PAGE_SIZE"].ToString() != "") ? int.Parse(reader["PAGE_SIZE"].ToString()) : 0, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()));
                initGridviewInfos.Add(initGridviewInfo);
            }
            return initGridviewInfos;
        }

        public List<InitGridviewInfo> GetInitGridviewByID(DataBase dataBase, DbTransaction tran, string functionID, string gridviewID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_INIT_GRIDVIEW + " WHERE IG.FUNCTION_ID = @FUNCTION_ID AND IG.GRIDVIEW_ID = @GRIDVIEW_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@FUNCTION_ID", functionID), new SqlParameter("@GRIDVIEW_ID", gridviewID) };
            List<InitGridviewInfo> initGridviewInfos = new List<InitGridviewInfo>();
            InitGridviewInfo initGridviewInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            while (reader.Read())
            {
                DateTime CS00002;
                initGridviewInfo = new InitGridviewInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["FUNCTION_ID"].ToString(), reader["MENU_STRING"].ToString(), reader["GRIDVIEW_ID"].ToString(), (reader["COLUMN_INDEX"].ToString() != "") ? int.Parse(reader["COLUMN_INDEX"].ToString()) : 0, reader["HEADER_TEXT"].ToString(), reader["FOOTER_TEXT"].ToString(), reader["DATA_FIELD"].ToString(), reader["CSS_CLASS"].ToString(), (reader["WIDTH"].ToString() != "") ? int.Parse(reader["WIDTH"].ToString()) : 0, (reader["PAGE_SIZE"].ToString() != "") ? int.Parse(reader["PAGE_SIZE"].ToString()) : 0, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()));
                initGridviewInfos.Add(initGridviewInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return initGridviewInfos;
        }

   

        public DataSet GetInitGridViewInfoByQueryEntity(List<string> sqlWhere, InitGridviewQueryEntity initGridviewQuery, string whLoginID)
        {
            string temp = this.SQL_SELECT_INIT_GRIDVIEW;
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!initGridviewQuery.IsGetAll)
            {
                //henrik2011-07-19修改
                temp = PagingHelper.GetPagingSQL(temp, initGridviewQuery.CurrentPage, initGridviewQuery.PageSize,
                                                 initGridviewQuery.SortField, initGridviewQuery.SortDirection);
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }
    }
}

